package com.web.server.db;

import cn.com.sgcc.dlt698.FreezeData;
import cn.com.sgcc.dlt698.data.Data;
import cn.com.sgcc.dlt698.data.OIA1;
import cn.com.sgcc.dlt698.data.OIVals;
import cn.com.sgcc.dlt698.util.NumberConvert;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.web.SpringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.PreparedStatement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class DBUtil {
    ObjectMapper mapper = new ObjectMapper();

    private final JdbcTemplate jdbcTemplate = SpringUtils.getBean(JdbcTemplate.class);

    static final int[] TYPES_BIGINT  = { Types.BIGINT };
    static final int[] TYPES_VARCHAR = { Types.VARCHAR };
    static final int[] TYPES_TINYINT = { Types.TINYINT };

    private static final List<OIA1> OIA1s = new ArrayList<>();

    static {
//        //正向有功电能
//        OIA1s.add(new OIA1("0010", 1, Map.of("06", 2, "15", 4)));
//        //反向有功电能
//        OIA1s.add(new OIA1("0020", 1, Map.of("06", 2, "15", 4)));
        //电压
        OIA1s.add(new OIA1("2000", 3, Map.of("06", 4, "12", 1)));
//        //电流
//        OIA1s.add(new OIA1("2001", 3, Map.of("05", 3)));
//        //有功功率
//        OIA1s.add(new OIA1("2004", 4, Map.of("05", 1)));
//        //无功功率
//        OIA1s.add(new OIA1("2005", 4, Map.of("05", 1)));
//        //功率因数
//        OIA1s.add(new OIA1("200A", 4, Map.of("10", 3)));
    }

    private String val2str(String type, String val, OIA1 oia1) {
        double dividend = type.equals("00") ? 0 : Math.pow(10, oia1.getAttr().get(type));

        String format = String.format("%%.%df", oia1.getAttr().get(type));

        return switch (type) {
            case "00" -> null;
            case "05" -> {
                int divisor = (int) Long.parseLong(val, 16);

                yield String.format(format, divisor / dividend);
            }
            case "06", "12" -> {
                long divisor = Long.parseLong(val, 16);

                yield String.format(format, divisor / dividend);
            }
            case "10" -> {
                short divisor = (short) Long.parseLong(val, 16);

                yield String.format(format, divisor / dividend);
            }
            default -> throw new RuntimeException("unsupported");
        };
    }

    private void dumpOIA1(String meterAddr,
                          String collectionDate,
                          String collectionTime,
                          List<Data> dataArray,
                          OIA1 oia1) {
//        if (dataArray.size() != oia1.getIc()) {
//            throw new RuntimeException("unsupported");
//        }

        for (int i = 0; i < dataArray.size(); i++) {
            var    data = dataArray.get(i);
            String v    = val2str(data.getType(), data.getVal(), oia1);
            if (v == null) {
                continue;
            }
            FreezeData freezeData = getFreezeData(meterAddr, collectionDate, oia1.getOi(), i);

            if (StringUtils.isEmpty(freezeData.getDatas().get(collectionTime))) {
                freezeData.getDatas().put(collectionTime, v);
            } else if (freezeData.getDatas().get(collectionTime).equalsIgnoreCase(v)) {
                //System.out.println("-----");
            } else {
                log.error("{} {} {}/{}", collectionTime, meterAddr, freezeData.getDatas().get(collectionTime), v);
                //throw new RuntimeException("unsupported");
            }

            updateFreezeData(freezeData);
        }
    }

    public synchronized void dump(List<Map<String, OIVals>> tables) {
        if (tables == null)
            return;
        for (var r : tables) {
            String meterAddr = r.get("202A").getVals().getFirst().getVal();

            String   dt             = NumberConvert.parse1C(r.get("6042").getVals().getFirst().getVal());
            String[] dtArr          = dt.split(" ");
            String   collectionDate = dtArr[0].replaceAll("-", "");
            String   collectionTime = dtArr[1];

            for (var oia1 : OIA1s) {
                OIVals oiVals = r.get(oia1.getOi());
                if (oiVals == null) {
                    continue;
                }
                dumpOIA1(meterAddr, collectionDate, collectionTime, oiVals.getVals(), oia1);
            }
        }
    }

    private FreezeData getFreezeData(String meterAddr, String collectionDate, String oi, int ic) {
        FreezeData freezeData = new FreezeData();

        freezeData.setMeterAddr(meterAddr);
        freezeData.setCollectionDate(collectionDate);
        freezeData.setOi(oi);
        freezeData.setIc(ic);

        do {
            String sql = "SELECT v FROM t_collect_data WHERE ic = ? and oi = ? and collection_date = ? and meter_addr = ?;";
            ArrayList<String> vs = (ArrayList<String>) jdbcTemplate.query(sql,
                    new Object[] { ic, oi, collectionDate, meterAddr }, new int[] {
                            Types.TINYINT, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR
                    }, (rs, rowNum) -> rs.getString(1));

            if (!vs.isEmpty()) {
                try {
                    freezeData.setDatas(mapper.readValue(vs.getFirst(), new TypeReference<TreeMap<String, String>>() {
                    }));
                } catch (JsonProcessingException e) {
                    throw new RuntimeException(e);
                }

                return freezeData;
            } else {
                freezeData.setDatas(new TreeMap<>());

                jdbcTemplate.update(connection -> {
                    String sql_ = "INSERT INTO t_collect_data (meter_addr, collection_date, oi, ic, v) VALUES (?, ?, ?, ?, '{}');";

                    PreparedStatement preparedStatement = connection.prepareStatement(sql_);
                    int               offset            = 1;

                    preparedStatement.setString(offset++, meterAddr);
                    preparedStatement.setString(offset++, collectionDate);
                    preparedStatement.setString(offset++, oi);
                    preparedStatement.setInt(offset++, ic);

                    return preparedStatement;
                });
            }
        } while (true);
    }

    private void updateFreezeData(FreezeData freezeData) {
        jdbcTemplate.update(connection -> {
            String sql = "UPDATE t_collect_data SET v = ?, cnt = ? WHERE ic = ?  and oi = ? and collection_date = ? and meter_addr = ?;";
            try {
                PreparedStatement preparedStatement = connection.prepareStatement(sql);

                int offset = 1;
                preparedStatement.setString(offset++, mapper.writeValueAsString(freezeData.getDatas()));
                preparedStatement.setInt(offset++, freezeData.getDatas().size());
                preparedStatement.setInt(offset++, freezeData.getIc());
                preparedStatement.setString(offset++, freezeData.getOi());
                preparedStatement.setString(offset++, freezeData.getCollectionDate());
                preparedStatement.setString(offset++, freezeData.getMeterAddr());

                return preparedStatement;
            } catch (JsonProcessingException e) {
                throw new RuntimeException(e);
            }
        });

    }

    //    public void insertDevices(String gwMac, List<Node> nodes) {
    //        //devices 需要默认有一条数据
    //        String sql = """
    //                INSERT INTO devices(`gw_mac`, `node_mac`)
    //                SELECT ?, ?
    //                FROM devices
    //                WHERE NOT EXISTS(SELECT 1 FROM devices WHERE node_mac = ?)
    //                LIMIT 1;""";
    //        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
    //            @Override
    //            public void setValues(@NotNull PreparedStatement preparedStatement, int i)
    //                    throws SQLException {
    //                preparedStatement.setString(1, gwMac);
    //                preparedStatement.setString(2, nodes.get(i).getMac());
    //                preparedStatement.setString(3, nodes.get(i).getMac());
    //            }
    //
    //            @Override
    //            public int getBatchSize() {
    //                return nodes.size();
    //            }
    //        });
    //    }
    //
    //    public void updateTopos(String gwMac, String uuid, List<Node> nodes, String payload) {
    //        List<String> ignoreNodes = getIgnoreNodes(gwMac);
    //
    //        Iterator<Node> iterator = nodes.iterator();
    //        while (iterator.hasNext()) {
    //            String mac = iterator.next().getMac();
    //            for (String s : ignoreNodes) {
    //                if (s.equalsIgnoreCase(mac)) {
    //                    iterator.remove();
    //                }
    //            }
    //        }
    //
    //        jdbcTemplate.update(connection -> {
    //            String sql = """
    //                    UPDATE topo_log_data SET report_nodes = ?, report_time=CURRENT_TIMESTAMP, report_topo=?,
    //                    expected_nodes=(SELECT COUNT(1) FROM devices WHERE state = 1 and gw_mac = ?)
    //                    WHERE uuid = ?;
    //                    """;
    //
    //            PreparedStatement preparedStatement = connection.prepareStatement(sql);
    //            preparedStatement.setInt(1, nodes.size());
    //            preparedStatement.setString(2, payload);
    //            preparedStatement.setString(3, gwMac);
    //            preparedStatement.setString(4, uuid);
    //            return preparedStatement;
    //        });
    //
    //        jdbcTemplate.batchUpdate("UPDATE device_log_data SET online = 1 WHERE node_mac = ? AND uuid = ?;",
    //                new BatchPreparedStatementSetter() {
    //                    @Override
    //                    public void setValues(PreparedStatement ps, int i)
    //                            throws SQLException {
    //                        ps.setString(1, nodes.get(i).getMac());
    //                        ps.setString(2, uuid);
    //                    }
    //
    //                    @Override
    //                    public int getBatchSize() {
    //                        return nodes.size();
    //                    }
    //                });
    //    }

    private ArrayList<String> getIgnoreNodes(String gwMac) {
        String sql = "SELECT node_mac FROM devices WHERE state = 0 AND gw_mac = ?";

        return (ArrayList<String>) jdbcTemplate.query(sql, new Object[] { gwMac, gwMac }, TYPES_VARCHAR,
                (rs, rowNum) -> rs.getString(1));
    }

    private ArrayList<String> getNodes(String gwMac) {
        String sql = "SELECT node_mac FROM devices WHERE state = 1 AND gw_mac != node_mac AND gw_mac = ?";

        return (ArrayList<String>) jdbcTemplate.query(sql, new Object[] { gwMac }, TYPES_VARCHAR,
                (rs, rowNum) -> rs.getString(1));
    }

    //    public void queryTopos(String gwMac, String uuid) {
    //        String collect_time = getNowTime();
    //
    //        KeyHolder keyHolder = new GeneratedKeyHolder();
    //        jdbcTemplate.update(connection -> {
    //            String sql = "INSERT INTO topo_log_data (gw_mac, uuid, collect_time) VALUES (?, ?, ?);";
    //
    //            PreparedStatement preparedStatement = connection.prepareStatement(sql, new String[] { "id" });
    //            preparedStatement.setString(1, gwMac);
    //            preparedStatement.setString(2, uuid);
    //            preparedStatement.setString(3, collect_time);
    //            return preparedStatement;
    //        }, keyHolder);
    //
    //        Number n = keyHolder.getKey();
    //        assert n != null;
    //
    //        List<String> expectedNodes = getNodes(gwMac);
    //        jdbcTemplate.batchUpdate(
    //                "INSERT INTO device_log_data(node_mac, collect_time, uuid, gw_mac) VALUES (?, ?, ?, ?)",
    //                new BatchPreparedStatementSetter() {
    //                    @Override
    //                    public void setValues(PreparedStatement ps, int i)
    //                            throws SQLException {
    //                        ps.setString(1, expectedNodes.get(i));
    //                        ps.setString(2, collect_time);
    //                        ps.setString(3, uuid);
    //                        ps.setString(4, gwMac);
    //                    }
    //
    //                    @Override
    //                    public int getBatchSize() {
    //                        return expectedNodes.size();
    //                    }
    //                });
    //    }

    public List<Map<String, Object>> getMeters(String date, String oi) {
        String sql = """
                SELECT meter_addr, cnt FROM t_collect_data WHERE ic = 0 AND oi = ? AND collection_date = ?
                """;

        return jdbcTemplate.queryForList(sql, new Object[] { oi, date }, new int[] { Types.VARCHAR, Types.VARCHAR });
    }

    public List<Map<String, Object>> getOIs(String date, String meterAddr) {
        String sql = """
                SELECT oi, cnt FROM t_collect_data WHERE ic = 0 AND meter_addr = ? AND collection_date = ?
                """;

        return jdbcTemplate.queryForList(sql, new Object[] { meterAddr, date },
                new int[] { Types.VARCHAR, Types.VARCHAR });
    }

    public List<Map<String, Object>> getV(String date, String meterAddr, String oi) {
        String sql = """
                SELECT v,  points FROM t_collect_data, t_meter_points\s
                WHERE t_meter_points.meter_addr = t_collect_data.meter_addr AND t_collect_data.ic = 0 AND
                t_collect_data.oi = ? AND t_collect_data.meter_addr = ? AND t_collect_data.collection_date = ?
                """;

        return jdbcTemplate.queryForList(sql, new Object[] { oi, meterAddr, date },
                new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR });
    }

    public List<Map<String, Object>> getNodeAbandonRate(String gwMac, String start, String end) {
        String sql = """
                SELECT node_mac, COUNT(1) as expected_cnt, sum(online) as real_sum, (COUNT(1) - sum(online)) as gap
                FROM device_log_data
                WHERE node_mac IN (SELECT node_mac FROM devices WHERE state = 1 AND node_mac != ? AND gw_mac = ?) AND
                collect_time > ? AND collect_time < ?
                GROUP BY(node_mac)
                ORDER BY gap DESC
                LIMIT 20;
                """;

        return jdbcTemplate.queryForList(sql, new Object[] { gwMac, gwMac, start, end },
                new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR });
    }

    public List<Map<String, Object>> getNodeLog(String nodeMac, String start, String end) {
        String sql = """
                SELECT DATE_FORMAT(collect_time,'%Y-%m-%d %H:%i:%S' ) as collect_time, online
                FROM device_log_data
                WHERE node_mac = ? AND collect_time > ? AND collect_time < ?
                ORDER BY collect_time DESC
                LIMIT 100;
                """;

        return jdbcTemplate.queryForList(sql, new Object[] { nodeMac, start, end },
                new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR });
    }

    public void disableNodeMac(String gwMac) {
        jdbcTemplate.update(connection -> {
            String sql = "DELETE FROM device_log_data WHERE node_mac = ?;";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, gwMac);

            return preparedStatement;
        });
        jdbcTemplate.update(connection -> {
            String sql = "UPDATE devices SET state = 0 WHERE node_mac = ?;";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, gwMac);

            return preparedStatement;
        });
    }

    public void enableNodeMac(String gwMac) {
        jdbcTemplate.update(connection -> {
            String sql = "UPDATE devices SET state = 1 WHERE node_mac = ?;";

            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, gwMac);

            return preparedStatement;
        });
    }

    private String getNowTime() {
        Date date = new Date();

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINA);
        return df.format(date);
    }

    // SELECT
    // collect_time, uuid,
    // json_extract(report_topo->'$.params.value.topos.nodes','$[*].mac') as macs,
    // json_extract(report_topo->'$.params.value.topos.nodes','$[*].tei') as tei,
    // json_extract(report_topo->'$.params.value.topos.nodes','$[*].proxy_tei') as proxy_tei
    // FROM topo_log_data ORDER BY collect_time DESC

    // 虚拟列
    // GENERATED ALWAYS AS (report_topo ->> '$.id')
}
